package com.seek.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

public class ExcelImport {

	public final static List<Object[]> readExcelData(MultipartFile file) throws Exception{
		
		Workbook workbook = null;
		try {
			if(file == null || file.isEmpty()){
				return null;
			}
			List<Object[]> list = new ArrayList<Object[]>();
			String filename = file.getOriginalFilename();
			if (filename.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(file.getInputStream());
			} else if (filename.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(file.getInputStream());
			} else {
				return null;
			}
			int numberOfSheets = workbook.getNumberOfSheets();
			for (int s = 0; s < numberOfSheets; s++) {
				Sheet sheet = workbook.getSheetAt(s);
				// 从第二行开始
				for (int r = sheet.getFirstRowNum() + 1; r <= sheet.getLastRowNum(); r++) {
					Row row = sheet.getRow(r);
					if (row == null)
						continue;
					Object[] cells = new Object[row.getLastCellNum()];
					for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
						Cell cell = row.getCell(c);
						if (cell == null)
							cells[c] = "";
						else
							cells[c] = getValue(cell);
					}
					list.add(cells);
				}
			}
			return list;
		} catch (Exception e) {
			throw e;
		}
	}
	
public final static List<Object[]> readExcelDataByFileName(String filename) throws Exception{
		
		Workbook workbook = null;
		try {
			 URL url = new URL(filename);  
	          HttpURLConnection uc = (HttpURLConnection) url.openConnection();  
	          uc.setDoInput(true);//设置是否要从 URL 连接读取数据,默认为true  
	          uc.connect();  
	          InputStream file = uc.getInputStream();  
			//FileInputStream file = new FileInputStream(new File(filename));
			if(filename == null || filename.isEmpty()){
				return null;
			}
			List<Object[]> list = new ArrayList<Object[]>();
			if (filename.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(file);
			} else if (filename.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(file);
			} else {
				return null;
			}
			int numberOfSheets = workbook.getNumberOfSheets();
			for (int s = 0; s < numberOfSheets; s++) {
				Sheet sheet = workbook.getSheetAt(s);
				// 从第二行开始
				for (int r = sheet.getFirstRowNum() + 1; r <= sheet.getLastRowNum(); r++) {
					Row row = sheet.getRow(r);
					if (row == null)
						continue;
					Object[] cells = new Object[row.getLastCellNum()];
					for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
						Cell cell = row.getCell(c);
						if (cell == null)
							cells[c] = "";
						else
							cells[c] = getValue(cell);
					}
					list.add(cells);
				}
			}
			return list;
		} catch (Exception e) {
			throw e;
		}
	}
	
	private static String getValue(Cell cell) {
		String value = null;
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_NUMERIC: // 数字
			if (HSSFDateUtil.isCellDateFormatted(cell)) { // 如果是日期类型
				value = new SimpleDateFormat("yyyyMMdd").format(cell.getDateCellValue());
			} else
				// BigDecimal dec = new
				// BigDecimal(cell.getNumericCellValue()).setScale(0,
				// BigDecimal.ROUND_HALF_UP);
				value = new BigDecimal(cell.getNumericCellValue()).toPlainString();
			break;
		case Cell.CELL_TYPE_STRING: // 字符串
			value = cell.getStringCellValue();
			break;
		case Cell.CELL_TYPE_FORMULA: // 公式
			// 用数字方式获取公式结果，根据值判断是否为日期类型
			double numericValue = cell.getNumericCellValue();
			if (HSSFDateUtil.isValidExcelDate(numericValue)) { // 如果是日期类型
				value = new SimpleDateFormat("yyyyMMdd").format(cell.getDateCellValue());
			} else
				value = new BigDecimal(numericValue).toPlainString();
			break;
		case Cell.CELL_TYPE_BLANK: // 空白
			value = "";
			break;
		case Cell.CELL_TYPE_BOOLEAN: // Boolean
			value = String.valueOf(cell.getBooleanCellValue());
			break;
		case Cell.CELL_TYPE_ERROR: // Error，返回错误码
			value = String.valueOf(cell.getErrorCellValue());
			break;
		default:
			value = "";
			break;
		}
		return value;
	}
}
